//
//  DataBaseHandle.m
//  UI_20_数据库
//
//  Created by dllo on 16/4/28.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle

//第一步:创建类 单列方法
+(DataBaseHandle *)shareDataBaseHandle{
    static DataBaseHandle *dataBase = nil;
    //在程序运行期间永远只会走一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataBase = [[DataBaseHandle alloc] init];
        
    });
    
    return dataBase;
}

//第二步:导入数据库的类库, 添加依赖libsqlite3.0.tbd

//第三步: .h引头文件   #import <sqlite3.h>

//第四步:创建一个数据库类型的指针
static sqlite3 *db = nil;

// 4 -1 打开数据库
-(void)openDB{
    //如果数据库对象存在则不需要往下进行
    if (db != nil) {
        return;
    }
    
    //获取沙盒document的路径
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    NSLog(@"%@",path);
    
    //拼接路径
    NSString *sqlitePath = [path stringByAppendingPathComponent:@"dataBase.sqlite"];
    
    //打开数据库函数,将数据库对象与数据库文件关联,并打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db); //名和地址
    
    //判断
    if (result == SQLITE_OK) {
       NSLog(@"数据库打开成功");
    }else{
       NSLog(@"数据库打开失败");
    }
}

-(void)openDBWithName:(NSString *)userName
{
    //如果数据库对象存在则不需要往下进行
    if (db != nil) {
        return;
    }
    
    NSString *fileName = [userName stringByAppendingString:@".sqlite"];
    NSString *documentsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    NSString *sqlitePath = [documentsPath stringByAppendingPathComponent:fileName];
    NSFileManager *manager = [NSFileManager defaultManager];
    BOOL needCreateTables = NO;
    if (![manager fileExistsAtPath:sqlitePath]) {
        needCreateTables = YES;
    }
    
    //打开数据库函数,将数据库对象与数据库文件关联,并打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db); //名和地址
    
    //判断
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
        return;
    }
    
    // 建表
    if (needCreateTables) {
        [self createDatabase];
    }
}

- (void)close
{
    sqlite3_close(db);
    db = nil;
}

- (void)createDatabase
{
    [self creatTableWithSQname:@"users"];
    [self creatDicountTableWithSQname:@"discount"];
}

//4 - 2 - 1 创建表 - User
-(void)creatTableWithSQname:(NSString *)sqName{
    //创建表的SQL语句
    NSString *sql = [NSString stringWithFormat:@"create table if not exists %@(number integer primary key autoincrement, pass text, userName text, pic text)",sqName];
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}

//4 - 2 - 2 创建表 - 折扣
-(void)creatDicountTableWithSQname:(NSString *)sqName{
    //创建表的SQL语句
    NSString *sql = [NSString stringWithFormat:@"create table if not exists %@(id text primary key , title text, price text, photo text, userName text)",sqName];
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}


//4-3 - 1 插入数据
-(void)insertDiscount:(DiscountModel *)discountModel withUserName:(NSString *)userName withSQname:(NSString *)sqName{

    //number 是主键, 我们不用给他赋值, 他自动递增
    NSString *sql = [NSString stringWithFormat:@"insert into %@(id, title, price, photo, userName) values('%@','%@','%@','%@','%@')", sqName, discountModel.discountId, discountModel.title, discountModel.price, discountModel.photo, userName];
    
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK ) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

//4-3 - 2  插入数据 打折详情
-(void)insertDiscountMore:(DiscountMoreHot_areaListModel *)discountModel withUserName:(NSString *)userName withSQname:(NSString *)sqName{
    
    //number 是主键, 我们不用给他赋值, 他自动递增
    NSString *sql = [NSString stringWithFormat:@"insert into %@(id, title, price, photo, userName) values('%@','%@','%@','%@','%@')", sqName, discountModel.idHot_area, discountModel.title, discountModel.price, discountModel.photo, userName];
    
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK ) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

//4-3 - 3  插入数据 打折详情 - 2
-(void)insertDiscountMore2:(DiscountMoreDidcount_topicListModel *)discountModel withUserName:(NSString *)userName withSQname:(NSString *)sqName{
    
    //number 是主键, 我们不用给他赋值, 他自动递增
    NSString *sql = [NSString stringWithFormat:@"insert into %@(id, title, price, photo, userName) values('%@','%@','%@','%@','%@')", sqName, discountModel.idlist, discountModel.title, discountModel.price, discountModel.photo, userName];
    
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK ) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

//4-3 - 4  插入数据 打折详情 - 2
-(void)insertDiscountMore3:(DiscountMoreHot_goodsModel *)discountModel withUserName:(NSString *)userName withSQname:(NSString *)sqName{
    
    //number 是主键, 我们不用给他赋值, 他自动递增
    NSString *sql = [NSString stringWithFormat:@"insert into %@(id, title, price, photo, userName) values('%ld','%@','%@','%@','%@')", sqName, (long)discountModel.idHot_goods, discountModel.title, discountModel.price, discountModel.photo, userName];
    
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK ) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}


//用户注册页面
-(void)insertMovie:(MineRegidterModel *)registerModel withSQname:(NSString *)sqName{
    //number 是主键, 我们不用给他赋值, 他自动递增
    NSString *sql = [NSString stringWithFormat:@"insert into %@(pass, userName, pic) values('%@', '%@', '%@')", sqName, registerModel.password, registerModel.userID, registerModel.pic];
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK ) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

/////4-5 删除数据
//-(void)deleteTable:(NSString *)title withSQname:(NSString *)sqName{
//    NSString *sql = [NSString stringWithFormat:@"delete from %@ where title = '%@'",sqName, title];
//    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
//    if (result == SQLITE_OK) {
//        NSLog(@"删除成功");
//    }else{
//        NSLog(@"删除失败");
//    }
//}

//4-6 查询所有数据---用户名@users
-(NSMutableArray *)selectAllWithSQname:(NSString *)sqName withCompare:(BOOL)compare{
    NSString *sql = [NSString stringWithFormat:@"select * from %@",sqName];
    //创建数据库的伴随指针
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, nil); //-1 不限制sql语句的长度
    NSMutableArray *yhmArray = [NSMutableArray array];
    NSMutableArray *allArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1); //提起字符串
            const unsigned char *userName = sqlite3_column_text(stmt, 2);
            const unsigned char *pic = sqlite3_column_text(stmt, 3);
            MineRegidterModel *disModel = [[MineRegidterModel alloc] init];
            if (userName != NULL) {
                disModel.userID = [NSString stringWithUTF8String:(const char *)userName];
            }
            
            if (title != NULL) {
                disModel.password = [NSString stringWithUTF8String:(const char *)title];
            }
            
            if (pic != NULL) {
                disModel.pic = [NSString stringWithUTF8String:(const char *)pic];
            }
            
            [yhmArray addObject:disModel.userID];
            [allArray addObject:disModel];
        }
        if (compare == YES) {
            return yhmArray;
        }else{
            return allArray;
        }
    }else{
        NSLog(@"准备失败");
        return nil;
    }
}

-(void)insert:(id)model withUserName:(NSString *)userName withSQname:(NSString *)sqName;
{
    
}

//4-7 条件查询   表@"discount"
-(NSMutableArray *)selectDiscount:(NSString *)userName withSQname:(NSString *)sqName{
    NSString *sql = [NSString stringWithFormat:@"select * from %@ where userName = '%@'",sqName, userName];
    //伴随指针
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(db, sql.UTF8String, -1,  &stmt, nil);
    NSMutableArray *arrayOne = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *disId = sqlite3_column_text(stmt, 0);
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *price = sqlite3_column_text(stmt, 2);
            const unsigned char *photo = sqlite3_column_text(stmt, 3);
            DiscountListModel *disListModel = [[DiscountListModel alloc] init];
            disListModel.discountId = [NSString stringWithUTF8String:(const char *)disId];
            disListModel.title = [NSString stringWithUTF8String:(const char *)title];
            disListModel.price = [NSString stringWithUTF8String:(const char *)price];
            disListModel.photo = [NSString stringWithUTF8String:(const char *)photo];
            [arrayOne addObject:disListModel];
        }
        return arrayOne;
    }else{
        NSLog(@"查询失败");
        return nil;
    }
}


//4-8 删除表
-(void)dropTabelWithSQname:(NSString *)sqName{
    NSString *sql = [NSString stringWithFormat:@"drop table %@",sqName];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}


// 4-6 判断是否已经收藏
- (BOOL)isSavedDiscount:(NSString *)discountID {
    NSString *sql = [NSString stringWithFormat:@"select * from discount where id = %@",discountID];
    
    //创建数据库的伴随指针
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"准备成功");
        BOOL saved = NO;
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            saved = YES;
            break;
        }
        return saved;
    }else{
        return NO;
    }
}

///4-5 删除数据
-(BOOL)deleteDiscountWithID:(NSString *)discountID userName:(NSString *)userName{
    NSString *sql = [NSString stringWithFormat:@"delete from discount where id = '%@' and  userName = '%@'", discountID, userName]; //id, title, price, photo, userName
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
        return YES;
    }else{
        NSLog(@"删除失败");
        return NO;
    }
}

@end
